package com.landis.temple.mobile;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TwitterStoryDatabase
{
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;
	private Context context;
	
	private static final String DATABASE_TITLE = "twitter_story";
	private static final int DATABASE_VERSION = 1;
	
	private static final String STORY_TABLE = "stories";
	private static final String CHAPTER_TABLE = "chapters";
	private static final String AUTHOR_TABLE = "authors";
	
	public static final String STORY_ID = "_id";
	public static final String STORY_AUTHOR_ID = "stories_authors_id";
	public static final String STORY_TITLE = "stories_title";
	public static final String STORY_DATE_CREATED = "stories_date_created";
	public static final String STORY_DATE_FINISHED = "stories_date_finished";
	
	public static final String CHAPTER_ID = "_id";
	public static final String CHAPTER_STORY_ID = "chapters_stories_id";
	public static final String CHAPTER_AUTHOR_ID = "chapters_authors_id";
	public static final String CHAPTER_DATE_CREATED = "chapters_date_created";
	public static final String CHAPTER_TEXT = "chapters_text";
	
	public static final String AUTHOR_ID = "_id";
	public static final String AUTHOR_NAME = "authors_name";
	
	private static final String CREATE_AUTHORS =  "CREATE TABLE " + AUTHOR_TABLE + " ( " +
												  AUTHOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT , " +
												  AUTHOR_NAME + " TEXT UNIQUE NOT NULL);";
	
	private static final String CREATE_CHAPTERS = "CREATE TABLE " + CHAPTER_TABLE + " ( " +
												  CHAPTER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
												  CHAPTER_STORY_ID + " INTEGER UNSIGNED NOT NULL," +
												  CHAPTER_AUTHOR_ID + " INTEGER UNSIGNED NOT NULL," +
												  CHAPTER_DATE_CREATED + " DATE NOT NULL DEFAULT ( CURRENT_DATE )," +
												  CHAPTER_TEXT + " TEXT NOT NULL," +
												  "FOREIGN KEY(" + CHAPTER_STORY_ID + ") REFERENCES " + 
												  STORY_TABLE + "(" + STORY_ID + ")," +
												  "FOREIGN KEY (" + CHAPTER_AUTHOR_ID + ") REFERENCES " + 
												  AUTHOR_TABLE + "(" + AUTHOR_ID + "));";
	
	private static final String CREATE_STORIES = "CREATE TABLE " + STORY_TABLE + " ( " +
	  											  STORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
	  											  STORY_AUTHOR_ID + " INTEGER UNSIGNED NOT NULL," +
	  											  STORY_TITLE + " TEXT NOT NULL," +
	  											  STORY_DATE_CREATED + " DATE NOT NULL DEFAULT ( CURRENT_DATE )," +
	  											  STORY_DATE_FINISHED + " DATE DEFAULT NULL," + 
	  											  "FOREIGN KEY (" + STORY_AUTHOR_ID + ") REFERENCES " + 
	  											  AUTHOR_TABLE + "(" + AUTHOR_ID + "));";
	
	private class DatabaseHelper extends SQLiteOpenHelper 
	{		
		public DatabaseHelper( Context context) 
		{
			super( context , DATABASE_TITLE , null, DATABASE_VERSION );
		}

		@Override
		public void onCreate(SQLiteDatabase database) 
		{
			database.execSQL(CREATE_AUTHORS);
			database.execSQL(CREATE_STORIES);
			database.execSQL(CREATE_CHAPTERS);
		}

		@Override
		public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) 
		{
			Log.w( 	this.getClass().getName() 				, 
					"Upgrading database from version " 
					+ oldVersion 
					+ " to " 
					+ newVersion 
					+ ", which will destroy all old data"	);
			
			database.execSQL( "DROP TABLE IF EXISTS " + AUTHOR_TABLE  + ";" );
			database.execSQL( "DROP TABLE IF EXISTS " + CHAPTER_TABLE + ";" );
			database.execSQL( "DROP TABLE IF EXISTS " + STORY_TABLE   + ";" );

			onCreate(database);
		}
	}
	
	public TwitterStoryDatabase( Context context )
	{
		this.context = context;
	}
	
	public TwitterStoryDatabase open() throws SQLException
	{
		dbHelper = new DatabaseHelper( context );
		db = dbHelper.getWritableDatabase();

		return this;
	}

	public void close()
	{
			dbHelper.close();
	}
	
	public long createAuthor( String name )
	{
	    name = escapeString( name );
	    
		ContentValues author = new ContentValues();
		author.put( AUTHOR_NAME , name );
		
		return db.insert( AUTHOR_TABLE , null , author );
	}
	
	public long createStory( long author_id , String title )
	{
	    title = escapeString( title );
	    
		ContentValues story = new ContentValues();		
		story.put( STORY_AUTHOR_ID , author_id );
		story.put( STORY_TITLE     , title     );
		
		return db.insert( STORY_TABLE , null , story );
	}
	
	public long createChapter( long story_id , long author_id , String text )
	{
	    // Safely escape string
	    text = escapeString( text );
	    
		ContentValues chapter = new ContentValues();
		
		chapter.put( CHAPTER_STORY_ID  , story_id  );
		chapter.put( CHAPTER_AUTHOR_ID , author_id );
		chapter.put( CHAPTER_TEXT      , text      );
		
		return db.insert( CHAPTER_TABLE , null , chapter );
	}
	
	public Cursor getChapters( long story_id )
	{	
		Cursor results =  db.query( 
				CHAPTER_TABLE 							,  // Table
				new String[] {  CHAPTER_ID           , 
				                CHAPTER_AUTHOR_ID    , 
				                CHAPTER_DATE_CREATED , 
				                CHAPTER_TEXT         } 	,  // Return Values
				CHAPTER_STORY_ID + " = " + story_id 	,  // Selection 
				null 									,  // Selection Args
				null 									,  // Group By
				null 									,  // Having
				CHAPTER_DATE_CREATED + " DESC" 			); // Order By
		
		if( results != null )
		{
			results.moveToFirst();
		}
		
		return results;
	}
	
	public Cursor getAllStories()
	{
		Cursor results =  db.query( 
				STORY_TABLE 							,  // Table
				new String[] {  STORY_ID            , 
				                STORY_AUTHOR_ID     , 
				                STORY_TITLE         , 
				                STORY_DATE_CREATED  }   ,  // Return Values
				null 							        ,  // Selection 
				null 									,  // Selection Args
				null 									,  // Group By
				null 									,  // Having
				STORY_DATE_CREATED + " DESC" 			); // Order By
		
		if( results != null )
		{
			results.moveToFirst();
		}
		else
		{
			Log.d("Debugging" , "getAllStoriesReturning null or 0");
		}
		
		return results;
	}
	
	public String getAuthor( long author_id )
	{
		Cursor results =  db.query(
		        true                            ,  // Distinct
				AUTHOR_TABLE 					,  // Table
				new String[] { AUTHOR_NAME }  	,  // Columns
				AUTHOR_ID + " = " + author_id   ,  // Selection 
				null 							,  // Selection Arguments
				null 							,  // Group By
				null 							,  // Having
				null						 	,  // Order By
				null                            ); // Limit
		
		if( results != null )
		{
		    results.moveToFirst();
		    return results.getString(0);
		}

		return null;
	}
	
	public Long getAuthorId( String author_name )
	{
	    author_name = escapeString(author_name);
	    
		Cursor results =  db.query( 
				true								,
				AUTHOR_TABLE 						,  // Table
				new String[] { AUTHOR_ID }  		,  // Return Values
				AUTHOR_NAME + "=" + author_name     ,  // Selection 
				null 								,  // Selection Args
				null 								,  // Group By
				null 								,  // Having
				null						 		,
				null); // Order By
		
		if( results != null )
		{
		    results.moveToFirst();
		    return results.getLong(0);
		}
		
		return null;
	}
	
	public void upgrade( int oldVersion , int newVersion)
	{
	    dbHelper.onUpgrade( db , oldVersion, newVersion );
	}
	
	private String escapeString( String danger )
	{
	    return danger;
	}
}
